﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

namespace SqliteDriver
{
	/*
		Class to help split up the implementation of the SQL server driver
		This handles the nitty gritty of assembling the SQL and marking up the DataTable
	*/
	class TableCommandProcessor
	{
		internal TableCommandProcessor(SqliteDataSource parent)
		{
			this.dataSource = parent;
		}

		/*
			Convert the view definition into SQL. This for the table data
		*/
		internal String TableDataCommandString(DbView.ViewContext context, String filterClause)
		{
			// getuseful stuff from the context
			DbView.ViewDefinition vp = context.ViewDef;
			String tableName = this.TableNameFromContext(context);
			// delegate to original version
			return this.TableDataCommandString(tableName, vp, filterClause);
		}

		/*
			Called directly to generate a sql string for updates.
		*/
		internal String TableDataCommandString(String tableName, DbView.ViewDefinition vp, String filterClause)
		{
			int top = 500; // prepare for parameterization

			// on initial display of data table there won't be a view definition yet
			if (vp == null)
				// bad sql, but it saves a separate query for fields.
				return String.Format("select * from {0} limit {1}", Helper.SafeTableName(tableName), top);

			// from here building Sql based on the current view definition.
			DbView.SchemaField[] display = vp.DisplayFields;
			String fieldList = "";
			foreach (DbView.SchemaField f in display)
			{
				if (fieldList.Length > 0) fieldList += ",";
				fieldList += f.SqlSafeName;
			}
			DbView.SchemaField[] sort = vp.SortFields;
			String sortClause = "";
			//String nolockClause = nolockDflt; // maybe, maybe not
			foreach (DbView.SchemaField f in sort)
			{
				if (sortClause.Length > 0) sortClause += ",";
				sortClause += f.SqlSafeName;
				if (f.Ord == "D")
					sortClause += " DESC";
			}
			if (sortClause.Length > 0)
			{
				sortClause = " ORDER BY " + sortClause;
			}

			String whereClause = "";

			// filter text search 
			if (filterClause.Length > 0)
			{
				// numeric test
				bool isNumeric = this.IsNumericSearch(filterClause);
				// filter clause logic
				DbView.SchemaField[] search = vp.SearchFields;
				if (isNumeric)
				{
					foreach (DbView.SchemaField f in search)
					{
						if (DbView.SchemaField.IsIntegerType(f.Type))
						{
							if (whereClause.Length > 0) whereClause += " or ";
							whereClause += String.Format("{0} = {1}", f.SqlSafeName, filterClause);
						}
					}
				}
				else
				{
					foreach (DbView.SchemaField f in search)
					{
						if (!DbView.SchemaField.IsIntegerType(f.Type))
						{
							if (whereClause.Length > 0) whereClause += " or ";
							// create a special form clause that can be modified to provide case-insensitive searching
							// in a case-sensitive database
							String clause = String.Format("[[UPPER({0}) like '%%']]", f.SqlSafeName);
							clause = SqliteTemplates.Templates.ProcessFilterClause(clause, filterClause);
							whereClause += clause;
							// WhereClause += String.Format("{0} like '%{1}%'", f.Name, FilterClause);
						}
					}
				}
				if (whereClause.Length > 0) whereClause = String.Format("({0})", whereClause);
			}
			// view definition extra search clause
			if (vp.ExtraClause.Length > 0)
			{
				if (whereClause.Length > 0) whereClause += " and ";
				whereClause += "(" + vp.ExtraClause + ")";
			}

			// additional ad-hoc filter clause
			if (vp.FilterClause.Length > 0)
			{
				if (whereClause.Length > 0) whereClause += " and ";
				whereClause += "(" + vp.FilterClause + ")";
			}

			if (whereClause.Length > 0) whereClause = " where " + whereClause;
			String sqlTemplate = @"
select {1} 
  from {0}
  {2}
  {3}
  limit {4}
";
			String sql = string.Format(sqlTemplate, Helper.SafeTableName(tableName), fieldList, whereClause, sortClause, top);
			return sql;
		}

		internal String MakeFieldsCommandString(DbView.ViewContext context, String sqlTemplate, String filterString)
		{
			// grab the table from the view context
			String tableName = this.TableNameFromContext(context);
			// assemble actual SQL from the template, table name and filter parameter.
			return AddFilterClause(String.Format(sqlTemplate, tableName), filterString);
		}

		internal String AggregateDataCommandString(String commandTemplate, DbView.ViewContext context)
		{
			DbView.ViewDefinition viewDef = context.ViewDef;
			DbView.SchemaField field = viewDef.SchemaFields[0];
			int top = 1000;
			String fieldName = field.Name;
			String tableName = viewDef.TableName;
			String nullField = this.MakeNullProxy(field);
			String filterClause = this.MakeFilterClause(context);
			String commandString = String.Format(commandTemplate, fieldName, tableName, context.FilterString.ToUpper(),
												top, nullField, filterClause);
			return commandString;
		}

		internal String RowCountCommandString(String commandTemplate, DbView.ViewContext context)
		{
			DbView.ViewDefinition viewDef = context.ViewDef;
			DbView.SchemaField field = viewDef.SchemaFields[0];
			String fieldName = field.Name;
			String tableName = viewDef.TableName;
			String filterClause = this.MakeFilterClause(context); // later.
			return String.Format(commandTemplate, fieldName, tableName, context.FilterString.ToUpper(),
														filterClause);
		}

		//////////////////////////////////////////////////////////////////////////////////////////////
		// The data-fetch part. This is called from the bakground thread kicked off by 
		// Mainpresenter.PopulateGrid() for any command that returns a table
		// 
		public DataTable BuildDataTable(DbView.Command command, String commandString, DbView.ViewContext context)
		{
			String tableName = this.TableNameFromContext(context);
			String connectionString = this.dataSource.ConnectionString;

			
			DataSet dataset = new DataSet();

			try
			{
				// Overwhelmingly normal branch is here where a SQL command is issued to the database.
				Helper.GenericMakeDset(connectionString, commandString, tableName, out dataset);
				// if fields all we have at the moment is the schema
				if (command.Cmd == DbView.Commands.Cmnd.Tables)
					SetRowCount(dataset, connectionString);
				if (command.Cmd == DbView.Commands.Cmnd.Fields)
					SetFieldInfo(connectionString, dataset, tableName, context.FilterString);
				if (command.Cmd == DbView.Commands.Cmnd.ForeignKeys)
					SetFKInfo(connectionString, dataset);
			}
			catch (Exception exc)
			{
				// handle as much as possible
				throw;  // rethrow an unanticipated SQL error (timeout, syntax error etc.
			}
			// and ad-hoc command did not generate a result set. Rather than encounter problems later 
			// attach an empty one to the result Dset. This will allow normal empty row processing later.
			if (command.Cmd == DbView.Commands.Cmnd.Adhoc && dataset.Tables.Count == 0)
			{
				dataset.Tables.Add();
			}
			//////////////////////////////////////////////////////////////////////////////////////////////////////
			// Following data=retrieval we can now use DataTable properties to mark-up the table in a way that the
			// display grid can recognise. Hence we can colour columns. rows and cells to make our point clearer.
			//
			DataTable table = dataset.Tables[0];
			// [Specialise: markup columns to be added as pop-up command parameters ]
			DbView.DataTableExtraInfoManager mgr = new DbView.DataTableExtraInfoManager(table);
			mgr.Command = commandString;
			//
			if (command.Cmd == DbView.Commands.Cmnd.TableData)
			{
				// decorate. This adds the coloured columns for PKey, FKey and Indexes.
				this.AddColumnMetaData(mgr);
			}
			//// below flags up other columns required as parameters for some pop-ups.
			//if (command.Cmd == DbView.Commands.Cmnd.ForeignKeys)
			//{
			//    mgr.SetParam(table.Columns[4], CmdParam.FkName);
			//    mgr.SetParam(table.Columns[0], CmdParam.FkTable);
			//    mgr.SetParam(table.Columns[1], CmdParam.FkField);
			//    mgr.SetParam(table.Columns[2], CmdParam.PkTable);
			//    mgr.SetParam(table.Columns[3], CmdParam.PkField);
			//}
			if (command.Cmd == DbView.Commands.Cmnd.AllSourceSearch)
			{
				table = this.DoObjectSearchTable(dataset, command);
			}
			return table;
		}

		private void SetRowCount(DataSet dataset, string connectionString)
		{
			DataTable tableDt = dataset.Tables[0];
			if (tableDt.Rows.Count == 0) return; // nothing to do
			List<string> statements = new List<string>();
			string template = "select '{0}' as n, (select count (*) from {0}) as c";
			foreach (DataRow dr in tableDt.Rows)
			{
				statements.Add(string.Format(template, dr[0]));
			}
			string sql = string.Join("\r\nunion ", statements.ToArray());
			DataTable rowData = Helper.GenericQuery(connectionString, sql);
			foreach (DataRow dr in tableDt.Rows)
			{
				string tt = dr[0].ToString();
				foreach (DataRow rcDr in rowData.Rows)
				{
					if (rcDr[0].ToString() == tt)
					{
						dr[1] = rcDr[1];
						break;
					}
				}
			}
		}

		private void SetFieldInfo(string connectionString, DataSet dataset, string tableName, string fieldFilter)
		{
			DataTable dt = ApplyFieldFilter(SqlTableSchema.MakeFieldInfoTable(connectionString, tableName), fieldFilter);
			
			dataset.Tables.Clear();
			dataset.Tables.Add(dt);
		}
		
		// here with a list of tables
		// this is accessed by the optional interface imple
		private void SetFKInfo(string connectionString, DataSet dataset)
		{
			DataTable tableDt = dataset.Tables[0];
			if (tableDt.Rows.Count == 0) return; // nothing to do. Hm returns the wrong format but hey!
			DataTable fkTable =TableCommandProcessor.GetTableFKInfo(connectionString, tableDt);
			dataset.Tables.Clear();
			dataset.Tables.Add(fkTable);
		}

		public static DataTable GetTableFKInfo(string connectionString, DataTable tableDt)
		{
			if (tableDt.Rows.Count == 0) return tableDt; // nothing to do. Hm returns the wrong format but hey!

			DataTable dt = null;
			string template = "PRAGMA foreign_key_list({0})";
			foreach (DataRow dr in tableDt.Rows)
			{
				string sql = string.Format(template, dr[0]);
				DataTable rowData = Helper.GenericQuery(connectionString, sql);
				if (rowData.Rows.Count > 0)
				{
					if (dt == null)
					{
						dt = rowData.Clone();
						DataColumn newColumn = new DataColumn("FKTable", typeof(System.String));
						newColumn.DefaultValue = "tba";
						dt.Columns.Add(newColumn);
						newColumn.SetOrdinal(0);
					}
					foreach (DataRow drFK in rowData.Rows)
					{
						dt.ImportRow(drFK);
						dt.Rows[dt.Rows.Count-1][0] = dr[0];
					}
				}
			}
			//
			dt.Columns.RemoveAt(1); // id
			dt.Columns.RemoveAt(1); // seq
			dt.Columns[2].SetOrdinal(1); // swap 
			dt.Columns["from"].ColumnName = "ForeignKey";
			dt.Columns["table"].ColumnName = "PKTable";
			dt.Columns["to"].ColumnName = "PrimaryKey";
			return dt;
		}

		private DataTable ApplyFieldFilter(DataTable dtAll, string fieldFilter)
		{
			// nothing to do if no filter
			if (String.IsNullOrEmpty(fieldFilter)) return dtAll;
			// Use datatable search
			// when GLOB implemented this will need to be extended to a regex.
			string searchField = dtAll.Columns[0].ColumnName;
			string select = string.Format("{1} like '%{0}%'", fieldFilter, Helper.SafeSqlField(searchField));
			DataRow[] rows = dtAll.Select(select);
			DataTable dt = dtAll.Clone();
			foreach (DataRow row in rows)
				dt.ImportRow(row);
			return dt;
		}

		void AddColumnMetaData(DbView.DataTableExtraInfoManager mgr)
		{
			// call a roiutine to query the database and construct the table.
			DataTable table = SqlTableSchema.MakeFieldInfoTable(this.dataSource.ConnectionString, mgr.Table.TableName);

			// check for pkeys
			int pkeyCount = 0;
			foreach (DataRow row in table.Rows)
				if (row["PKey?"].ToString().Length > 2) ++pkeyCount;
			bool modifiable = (pkeyCount > 0); // can't modify a table w/o a pkey
			// mark up pkeys, indexes and fkeys for colouring
			foreach (DataColumn col in mgr.Table.Columns)
			{
				String search = String.Format("[Field Name]='{0}'", col.ColumnName);
				DataRow[] row = table.Select(search);
				if (row.GetLength(0) > 0)
				{
				    if (row[0]["PKey?"].ToString().Length > 2) // ie YES not NO
				    {
				        mgr.SetPkey(col);
				        --pkeyCount; // counting down to 0
				    }
				    if (row[0]["FKey?"].ToString().Length > 2)
				        mgr.SetFkey(col);
				    if (row[0]["Index?"].ToString().Length > 2)
				        mgr.SetIndexed(col);
				}
				if (modifiable && pkeyCount == 0) // not all pkeys are in the view
				    mgr.SetModifiable();
			}
		}

		///////////////////////////////////////////////////////////////////////
		// Helpers
		// used by parent 
		//internal String AddFilterClause(String command, String filterText)
		//{
		//    // delegate to template processor
		//    if (HasModifiedWhereClause(command))
		//        return SqliteTemplates.Templates.ProcessFilterClause(command, filterText);
		//    return String.Format(command, filterText); // what about Case sensitive.
		//}

		static internal String AddFilterClause(String command, String filterText)
		{
			// delegate to template processor
			if (HasModifiedWhereClause(command))
				return SqliteTemplates.Templates.ProcessFilterClause(command, filterText);
			return String.Format(command, filterText); // what about Case sensitive.
		}

		// Detecting templates where the only configuration is the filter string
		internal bool SimpleTemplate(String commandTemplate)
		{
			if (commandTemplate.Length == 0) return false; // don't know what this condition is about - bad!
			// for fancy filters the filterstring is not added by string format
			if (HasModifiedWhereClause(commandTemplate))
			{
				if (commandTemplate.IndexOf("{0}") == -1)
					return true;
			}
			else
				// {0} is for the filter string therefore if there is no {1} this is simple
				if (commandTemplate.IndexOf("{1}") == -1)
					return true;
			return false;
		}

		// Templates include special mark-up this knows that [[ ]] encloses a where clause that has a variety of flavours. See below
		static bool HasModifiedWhereClause(String commandTemplate) { return (commandTemplate.IndexOf("[[") > 0); }

		String TableNameFromContext(DbView.ViewContext context)
		{
			// if there is a non-null view definition the table is 
			// there.
			if (context.ViewDef != null) return context.ViewDef.TableName;
			// otherwise this is (hopefully) a popup from the table list
			else return context.ObjectName;
		}

		bool IsNumericSearch(String filter)
		{
			Int64 bigint;
			return Int64.TryParse(filter, out bigint);
		}

		private string MakeFilterClause(DbView.ViewContext context)
		{
			// add the Filter clause.
			DbView.ViewDefinition vp = context.ViewDef;
			String extraClause = vp.ExtraClause;
			if (extraClause.Length > 0 && vp.FilterClause.Length > 0)
				extraClause += " AND ";
			if (vp.FilterClause.Length > 0)
				extraClause += vp.FilterClause;
			if (extraClause.Length == 0) return "";
			return " AND " + extraClause;
		}

		private String MakeNullProxy(DbView.SchemaField field)
		{
			return (field.Type == "S") ? "`" : // choose an unlikely character.
			(field.Type == "I") ? "-8989" :  // random very unusual value.
			(field.Type == "J") ? "-89898989" :  // random very unusual value (bigint)
			(field.Type == "N") ? "-8989.9898" :  // random very unusual value.
			(field.Type == "X") ? "239" :     // will be inaccurate, but no choice
			(field.Type == "D") ? "19700102"
							   : "0"; // this should suit all
		}

		// New from pre-search of source for tables or columns make anexact search
		// test results against RedGate product
		// Tidy up source form
		//  1. get whole word turned on by default or show whole words if any match
		//  2. get next and prev and match count taking account of option.
		private DataTable DoObjectSearchTable(DataSet dataset, DbView.Command command)
		{
			// extract the search from the command
			String tgt = command["SEARCH"].ToUpper(); // extract the search from the command
			// build an appropriate Regex
			String before = @"(^|[\s|'|.|!|""|%|&|(|*|+|,|\-|||:|;|<|=|>|\^|[])";    // keep this in sync with the version in SourceForm2.cs
			String after = @"([\s|'|.|!|""|%|&|)|*|+|,|\-|||:|;|<|=|>|\^|\]]|$)"; // keep this in sync with the version in SourceForm2.cs
			String pattern = before + tgt + after;
			DbView.SimpleRegex search = new DbView.SimpleRegex(pattern);
			// Input
			DataTable dt = dataset.Tables[0];
			// output filtered by first true match only
			DataTable dt2 = Helper.MakeDataTable(dt.TableName, "ObjectName|Type|Source", "SSS");
			String lastObject = "";  // hold last object matched to prevent duplicate hits in large source
			foreach (DataRow dr in dt.Rows)
			{
				String objectName = dr[0].ToString();
				if (lastObject != objectName)
				{
					String sourceCode = dr[2].ToString();
					String objectType = dr[1].ToString();
					String matchLine = this.FirstMatch(sourceCode, search);
					if (matchLine.Length > 0) // match if non-zero length string returned.
					{
						dt2.Rows.Add(new object[] { objectName, objectType, matchLine });
						lastObject = objectName;
					}
				}
			}
			return dt2;
		}

		private string FirstMatch(string sourceCode, DbView.SimpleRegex search)
		{
			String[] lines = sourceCode.Split('\n');
			foreach (String line in lines)
			{
				if (search.IsMatch(line.ToUpper()))
					return line;
			}
			return "";
		}

		SqliteDataSource dataSource;
	}
}
